package com.sophossystems.client.asmon.dao.spring;

import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

import com.sophossystems.client.asmon.dao.RepresentanteLegalDao;
import com.sophossystems.client.asmon.dto.RepresentanteLegalDTO;
import com.sophossystems.client.asmon.dto.RepresentanteLegalPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.RepresentanteLegal;

public class RepresentanteLegalDaoImpl extends AbstractDAO implements
		RepresentanteLegalDao {

	
	
	private static Logger log = Logger.getLogger(RepresentanteLegalDaoImpl.class);
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ActivityPk
	 */
	@Override
	@Transactional
	public RepresentanteLegalPkDTO insert(final RepresentanteLegalDTO dto) {
		RepresentanteLegalPkDTO exito = null;
		try {
			exito = (RepresentanteLegalPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							Transaction tx = session.beginTransaction();
							RepresentanteLegalPkDTO pk = null;
							RepresentanteLegal representante = new RepresentanteLegal();
							representante.mapper(dto);
							session.save(representante);
							tx.commit();

							try {
								RepresentanteLegalPkDTO res = findLastInsert();
								pk = new RepresentanteLegalPkDTO();
								pk.setIdRepLegal(res.getIdRepLegal());
							} catch (DaoException es) {
								throw new HibernateException("Insercion: "
										+ es.getMessage());
							}

							return pk;

						}

					});
			// / return exito;
		} catch (final Exception e) {
			logger.error("Error: getRepresentante Legal, cause: " + e.getMessage(), e);

		}
		return exito;
	}

	/**
	 * Updates a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void update(final RepresentanteLegalDTO dto) throws DaoException {
		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					RepresentanteLegalPkDTO pk = null;
					RepresentanteLegalDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = (RepresentanteLegalDTO) findWhereIdRepLegalEquals(dto
								.getIdRepLegal()).get(0);
						pk = new RepresentanteLegalPkDTO();
						pk.setIdRepLegal(res.getIdRepLegal());
					} catch (DaoException es) {
						throw new HibernateException("Update RepresentanteLegalDao: "
								+ es.getMessage());
					}
					// /aqui se actualizar los campos y con los de la entrada
					// cuidado de actualiza todo.
					RepresentanteLegal persis = new RepresentanteLegal();
					persis.mapper(dto);
					persis.setIdRepLegal(res.getIdRepLegal());
					session.update(persis);
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: update Representante, cause: " + e.getMessage(), e);
			throw new DaoException("Error: update Representante, cause: "
					+ e.getMessage());
		}

	}

	/**
	 * Deletes a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void delete(final RepresentanteLegalPkDTO pk) throws DaoException {

		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					Transaction tx = session.beginTransaction();
					RepresentanteLegal representante = (RepresentanteLegal) session
							.load(RepresentanteLegal.class, pk.getIdRepLegal());
					session.delete(representante);
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: "
					+ e.getMessage());
		}
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName() {
		return "RepresentanteLegal";
	}

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'idRepLegal = :idRepLegal'.
	 * 
	 * @Transactional public RepresentanteLegalDTO findByPrimaryKey(int
	 *                idRepLegal) throws DaoException { try {
	 *                List<RepresentanteLegalDTO> list = jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() + " WHERE idRepLegal = ?",
	 *                this,idRepLegal); return list.size() == 0 ? null :
	 *                list.get(0); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	@Override
	@SuppressWarnings("unchecked")
	/** 
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the criteria ''.
	 */
	@Transactional
	public List<RepresentanteLegalDTO> findAll() throws DaoException {
		List<RepresentanteLegalDTO> representante = null;
		try {
			representante = (List<RepresentanteLegalDTO>) getHibernateTemplate()
					.execute(new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT rl.idRepLegal as idRepLegal, rl.idCliente as idCliente, rl.aPaterno as aPaterno,"
											+ " rl.aMaterno as aMaterno, rl.nombres as nombres, rl.fNacimiento as fNacimiento, rl.fInicioActividad as fInicioActividad, rl.email as email, rl.rfc as rfc, rl.curp as curp,"
											+ " rl.numTel as numTel, rl.idDireccion as idDireccion,rl.sexo as sexo, rl.rfcCliente as rfcCliente, rl.ext1 as ext1 FROM "
											+ getTableName()
											+ " rl ORDER BY rl.idRepLegal");
							List<RepresentanteLegalDTO> lista = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(RepresentanteLegalDTO.class))
									.list();
							return lista;
						}
					});
			// return
			// jdbcTemplate.query("SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
			// + getTableName() + " ORDER BY idRepLegal", this);
		} catch (Exception e) {
			logger.error(
					"Error: REPRESENTANTE_LEGAL, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return representante;
	}

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'idRepLegal = :idRepLegal'.
	 */
	@Transactional
	@SuppressWarnings("unchecked")
	public List<RepresentanteLegalDTO> findWhereIdRepLegalEquals(
			final int idRepLegal) throws DaoException {
		List<RepresentanteLegalDTO> representante = null;
		try {
			representante = (List<RepresentanteLegalDTO>) getHibernateTemplate()
					.execute(new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT rl.idRepLegal as idRepLegal, rl.idCliente as idCliente, rl.aPaterno as aPaterno,"
											+ " rl.aMaterno as aMaterno, rl.nombres as nombres, rl.fNacimiento as fNacimiento, " +
											"rl.fInicioActividad as fInicioActividad, rl.email as email, rl.rfc as rfc, rl.curp as curp,"
											+ " rl.numTel as numTel, rl.sexo as sexo,rl.idDireccion as idDireccion, rl.rfcCliente as rfcCliente, rl.ext1 as ext1 FROM "
											+ getTableName()
											+ " rl WHERE rl.idRepLegal = :idRepLegal ORDER BY idCliente");
							oQuery.setInteger("idRepLegal", idRepLegal);
							List<RepresentanteLegalDTO> representanteDto = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(RepresentanteLegalDTO.class))
									.list();
							return representanteDto;
						}
					});

		} catch (Exception e) {
			logger.error(
					"Error: REPRESENTANTE_LEGAL, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return representante;

		/*
		 * try { return jdbcTemplate.query(
		 * "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
		 * + getTableName() + " WHERE idRepLegal = ? ORDER BY idRepLegal",
		 * this,idRepLegal); } catch (Exception e) {
		 * logger.error("Error: REPRESENTANTE_LEGAL, cause: " + e.getMessage(),
		 * e); throw new DaoException("Query failed", e); }
		 */

	}

	@Override
	@SuppressWarnings("unchecked")
	/** 
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the criteria 'idCliente = :idCliente'.
	 */
	@Transactional
	public List<RepresentanteLegalDTO> findWhereIdClienteEquals(
			final int idCliente) throws DaoException {
		List<RepresentanteLegalDTO> representante = null;
		try {
			representante = (List<RepresentanteLegalDTO>) getHibernateTemplate()
					.execute(new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT rl.idRepLegal as idRepLegal, rl.idCliente as idCliente, rl.aPaterno as aPaterno,"
											+ " rl.aMaterno as aMaterno, rl.nombres as nombres, rl.fNacimiento as fNacimiento, rl.fInicioActividad as fInicioActividad, rl.email as email, rl.rfc as rfc, rl.curp as curp,"
											+ " rl.numTel as numTel, rl.idDireccion as idDireccion, rl.sexo as sexo,rl.rfcCliente as rfcCliente, rl.ext1 as ext1 FROM "
											+ getTableName()
											+ " rl WHERE idCliente = :cliente ORDER BY idCliente");
							oQuery.setInteger("cliente", idCliente);
							List<RepresentanteLegalDTO> representanteDto = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(RepresentanteLegalDTO.class))
									.list();
							return representanteDto;
						}
					});
			// return
			// jdbcTemplate.query("SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
			// + getTableName() + " WHERE idCliente = ? ORDER BY idCliente",
			// this,idCliente);
		} catch (Exception e) {
			logger.error(
					"Error: REPRESENTANTE_LEGAL, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return representante;
	}

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'aPaterno = :apaterno'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereApaternoEquals(String apaterno) throws
	 *                DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() +
	 *                " WHERE aPaterno = ? ORDER BY aPaterno", this,apaterno); }
	 *                catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'aMaterno = :amaterno'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereAmaternoEquals(String amaterno) throws
	 *                DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() +
	 *                " WHERE aMaterno = ? ORDER BY aMaterno", this,amaterno); }
	 *                catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'nombres = :nombres'.
	 */
	@Transactional
	@SuppressWarnings("unchecked")
	public List<RepresentanteLegalDTO> findWhereNombresEquals(
			final String nombres, final String apellidoPaterno,
			final String apellidoMaterno) throws DaoException {
		List<RepresentanteLegalDTO> representante = null;
		try {
			representante = (List<RepresentanteLegalDTO>) getHibernateTemplate()
					.execute(new HibernateCallback() {

						@Override
						public Object doInHibernate(final Session session)
								throws HibernateException, SQLException {

							final Query oQuery = session
									.createQuery("SELECT rl.idRepLegal as idRepLegal, rl.idCliente as idCliente, rl.aPaterno as aPaterno,"
											+ " rl.aMaterno as aMaterno, rl.nombres as nombres, rl.fNacimiento as fNacimiento, rl.fInicioActividad as fInicioActividad, rl.email as email, rl.rfc as rfc, rl.curp as curp,"
											+ " rl.numTel as numTel, rl.idDireccion as idDireccion,rl.sexo as sexo, rl.rfcCliente as rfcCliente, rl.ext1 as ext1 FROM "
											+ getTableName()
											+ " rl WHERE rl.aPaterno = :aPaterno and  rl.aMaterno=:aMaterno and rl.nombres = :nombres ");
							oQuery.setString("aPaterno", apellidoPaterno)
									.setString("aMaterno", apellidoMaterno)
									.setString("nombres", nombres);
							final List<RepresentanteLegalDTO> representanteDto = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(RepresentanteLegalDTO.class))
									.list();
							return representanteDto;
						}
					});
		} catch (Exception es) {
			logger.error("Eception FindWhereNombresEquals: " + es.getMessage());
		}
		return representante;

	}

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'fNacimiento = :fnacimiento'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereFnacimientoEquals(Date fnacimiento) throws
	 *                DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() +
	 *                " WHERE fNacimiento = ? ORDER BY fNacimiento",
	 *                this,fnacimiento); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'fInicioActividad = :finicioactividad'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereFinicioactividadEquals(Date finicioactividad)
	 *                throws DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() +
	 *                " WHERE fInicioActividad = ? ORDER BY fInicioActividad",
	 *                this,finicioactividad); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'email = :email'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereEmailEquals(String email) throws DaoException {
	 *                try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() + " WHERE email = ? ORDER BY email",
	 *                this,email); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'rfc = :rfc'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereRfcEquals(String rfc) throws DaoException { try {
	 *                return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() + " WHERE rfc = ? ORDER BY rfc",
	 *                this,rfc); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'curp = :curp'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereCurpEquals(String curp) throws DaoException { try
	 *                { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() + " WHERE curp = ? ORDER BY curp",
	 *                this,curp); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'numTel = :numTel'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereNumTelEquals(int numTel) throws DaoException {
	 *                try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() + " WHERE numTel = ? ORDER BY numTel",
	 *                this,numTel); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'idDireccion = :idDireccion'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereIdDireccionEquals(int idDireccion) throws
	 *                DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() +
	 *                " WHERE idDireccion = ? ORDER BY idDireccion",
	 *                this,idDireccion); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_LEGAL table that match the
	 * criteria 'rfcCliente = :rfcCliente'.
	 * 
	 * @Transactional public List<RepresentanteLegalDTO>
	 *                findWhereRfcClienteEquals(String rfcCliente) throws
	 *                DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, aPaterno, aMaterno, nombres, fNacimiento, fInicioActividad, email, rfc, curp, numTel, idDireccion, rfcCliente FROM "
	 *                + getTableName() +
	 *                " WHERE rfcCliente = ? ORDER BY rfcCliente",
	 *                this,rfcCliente); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_LEGAL, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns the rows from the REPRESENTANTE_LEGAL table that matches the
	 * specified primary-key value.
	 */
	@Override
	public RepresentanteLegalDTO findByPrimaryKey(RepresentanteLegalPkDTO pk)
			throws DaoException {
		return this.findWhereIdRepLegalEquals(pk.getIdRepLegal()).get(0);
	}

	@Override
	public RepresentanteLegalPkDTO findLastInsert() throws DaoException {
		RepresentanteLegalPkDTO representanteLegarl = null;
		try {
			representanteLegarl = (RepresentanteLegalPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							
							Query oQuery = session
									.createQuery("SELECT max(r.idRepLegal) as idRepLegal FROM "
											+ getTableName()
											+ " r ");
							
							RepresentanteLegalPkDTO representanteLegarlDto = (RepresentanteLegalPkDTO) oQuery
									.setResultTransformer(Transformers
											.aliasToBean(RepresentanteLegalPkDTO.class)).list().get(0);
							return representanteLegarlDto;
						}
					});
		
		} catch (Exception e) {
			logger.error("Error: RepresentanteLegalDaoImpl, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return representanteLegarl;

	}

	/* (non-Javadoc)
	 * @see com.sophossystems.client.asmon.dao.RepresentanteLegalDao#findByPrimaryKey(int)
	 */
	@Override
	public RepresentanteLegalDTO findByPrimaryKey(int idRepLegal)
			throws DaoException {
		
		return this.findWhereIdRepLegalEquals(idRepLegal).get(0);
	}
	
}
